package common;

import java.awt.Dimension;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;

import db.DBConnection;
import db.DBInteraction;

public class Utility {
	
	/**
	 * This final static value represents the default width of the frame
	 */
	final static int DEFAULT_FRAME_WIDTH = 1382;
	/**
	 * This final static value represents the default height of the frame
	 */
	final static int DEFAULT_FRAME_HEIGHT = 744;

	/**
	 * This function returns the list of the cities stored on database
	 * @param dbCon - the connection to database
	 * @return The list of cities stored in database, or null if there aren't
	 */
	public static String[] loadCitiesFromDB(DBConnection dbCon) 
	{
		try {
			String query = "SELECT COUNT(*) FROM Comune;";
			ResultSet res = DBInteraction.executeQuery(dbCon, query);
			res.next();
			String[] comuni = new String[res.getInt(1)];
			query = "SELECT Nome FROM Comune;";
			res = DBInteraction.executeQuery(dbCon, query);
			int i=0;
			while(res.next()) 
			{
				comuni[i++] = res.getString(1);
			}
			return comuni;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * This function returns the list of the cities stored on database
	 * @param dbCon - the connection to database
	 * @param limit - the limit of characters of each city (which are cutted)
	 * @return The list of cities stored in database, or null if there aren't
	 */
	public static String[] loadCitiesFromDBWithCharLimit(DBConnection dbCon, int limit) 
	{
		try {
			String query = "SELECT COUNT(*) FROM Comune;";
			ResultSet res = DBInteraction.executeQuery(dbCon, query);
			res.next();
			String[] comuni = new String[res.getInt(1)];
			query = "SELECT Nome FROM Comune;";
			res = DBInteraction.executeQuery(dbCon, query);
			int i=0;
			String tempCity;
			while(res.next()) 
			{
				tempCity = res.getString(1);
				if(tempCity.length() <= limit)
					comuni[i++] = res.getString(1);
				else
				{
					tempCity = res.getString(1).substring(0, limit-2);
					comuni[i++] = tempCity + "..";
				}
			}
			return comuni;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * This function gets the city's cap
	 * @param dbCon, the connection to database
	 * @param city, the given city to query database
	 * @return the cap of the city or null if there is an error
	 */
	public static String getCAPFromDB(DBConnection dbCon, String city)
	{
		String query = "SELECT CAPCOD FROM Comune WHERE Nome LIKE ?;";
		ResultSet res = DBInteraction.executeParametricQuerySingleParameter(dbCon, query, city);
		try {
			res.next();
			return res.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * This function gets the city's provence
	 * @param dbCon, the connection to database
	 * @param city, the given city to query database
	 * @return the povence of the city or null if there is an error
	 */
	public static String getProvinciaFromDB(DBConnection dbCon, String city)
	{
		String query = "SELECT ProvinciaS FROM Comune WHERE Nome LIKE ?;";
		ResultSet res = DBInteraction.executeParametricQuerySingleParameter(dbCon, query, city);
		try {
			res.next();
			return res.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * Questa funzione converte un oggetto Calendar in una stringa riportante la data nel formato europeo (dd/mm/yyyy)
	 * @param date
	 * @return
	 */
	public static String convertCalendarToEuropeanDate(Calendar date)
	{
		String data = "";
		data += date.get(Calendar.DAY_OF_MONTH) + "/";
		if((date.get(Calendar.MONTH)+1) < 10)
			// We have to add a 0 in the string
			data += "0" + (date.get(Calendar.MONTH)+1);
		else
			data += (date.get(Calendar.MONTH)+1);
		data += "/";
		data += date.get(Calendar.YEAR);
		return data;
	}
	
	/**
	 * This function converts a Calendar date into the accepted format in H2DB (yyyy-mm-dd).
	 * Note(corrected bug): the month is now right (because of it starts from 0 in Calendar and it must be
	 * added by one before ad it to the string.
	 * @param date
	 * @return the converted date in String format
	 */
	public static String convertDateFormat(Calendar date)
	{
		String data = "";
		data += date.get(Calendar.YEAR) + "-";
		if((date.get(Calendar.MONTH)+1) < 10)
			// We have to add a 0 in the string
			data += "0" + (date.get(Calendar.MONTH)+1);
		else
			data += (date.get(Calendar.MONTH)+1);
		
		data += "-";
		
		if(date.get(Calendar.DAY_OF_MONTH) < 10)
			// We have to add a 0 in the string 
			data += "0" + date.get(Calendar.DAY_OF_MONTH);
		else
			data += date.get(Calendar.DAY_OF_MONTH);
		
		return data;
	}
	
	/**
	 * This function converts a date from american format to european format
	 * @param americanDate - the date in american format yyyy-mm-dd
	 * @return the european format of the date (dd/mm/yyyy)
	 */
	public static String convertStringData(String americanDate)
	{
		String[] data = americanDate.split("-");
		String euroData = data[2] + "/" + data[1] + "/" + data[0];
		return euroData;
	}
	
	public static Dimension resizeObjectToCurrentDimension(Dimension oldObjectSize, Dimension currentWindowSize)
	{
		Dimension newDim = new Dimension();
		int dimRatio = currentWindowSize.width / Utility.DEFAULT_FRAME_WIDTH;
		newDim.setSize(oldObjectSize.getWidth()*dimRatio, oldObjectSize.getHeight()*dimRatio);
		
		return newDim;
	}
	
	/**
	 * This function returns the list of the "costruttori" stored on database
	 * @param dbCon, the connection to database
	 * @return The list of "costruttori" stored in database, or null if there aren't
	 */
	public static String[] loadCostruttoriFromDB(DBConnection dbCon) 
	{
		try {
			String query = "SELECT COUNT(*) FROM Costruttore;";
			ResultSet res = DBInteraction.executeQuery(dbCon, query);
			res.next();
			String[] costruttori = new String[res.getInt(1)];
			query = "SELECT Nome, CodiceCostruttore FROM Costruttore;";
			res = DBInteraction.executeQuery(dbCon, query);
			int i=0;
			while(res.next()) 
			{
				costruttori[i++] = res.getString(1) + " (" + res.getString(2) + ")";
			}
			return costruttori;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * This function allows to get all the stored models from database
	 * @param dbCon - the connection to database
	 * @return modelli, a string[] that contains all the models stored, null otherwise
	 */
	public static String[] getModelliFromDB(DBConnection dbCon)
	{
		int limit = 13;
		String query = "SELECT COUNT(*) FROM Modello";
		ResultSet res = DBInteraction.executeQuery(dbCon, query);
		try {
			res.next();
			String [] modelli = new String[res.getInt(1)];
			query = "SELECT Modello, CodiceModello, Nome FROM Modello, Costruttore WHERE Modello.CodiceCostruttore = Costruttore.CodiceCostruttore";
			res = DBInteraction.executeQuery(dbCon, query);
			int i=0;
			String tempModel;
			while(res.next())
			{
				tempModel =  "(" + res.getString(2) + ") " + res.getString(1);
				if(tempModel.length() <= limit)
					modelli[i++] = tempModel;
				else
				{
					tempModel = tempModel.substring(0, limit-2);
					modelli[i++] = tempModel + "..";
				}
			}
			return modelli;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * This function allows to get all the stored states from database
	 * @param dbCon - the connection to database
	 * @return stati, a string[] that contains all the states stored, null otherwise
	 */
	public static String[] getStatiFromDB(DBConnection dbCon)
	{
		String query = "SELECT COUNT(*) FROM StatoImpianto";
		ResultSet res = DBInteraction.executeQuery(dbCon, query);
		try {
			res.next();
			String[] stati = new String[res.getInt(1)];
			query = "SELECT NomeStato FROM StatoImpianto";
			res = DBInteraction.executeQuery(dbCon, query);
			int i=0;
			while(res.next())
			{
				stati[i++] = res.getString(1);
			}
			return stati;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * This function allows to get the year from a string formatted [YYYY-MM-DD]
	 * @param date - the date in this format [YYYY-MM-DD]
	 * @return the integer that represents the year of the given date
	 */
	public static int getYearFromStringDate(String date)
	{
		String [] elementi = date.split("-");
		return Integer.parseInt(elementi[0]);
	}
	
	/**
	 * Questa funzione converte un valore monetario dal formato visualizzato (es. 100,00) al formato 
	 * utilizzabile nel database (es. 100.00)
	 * @param valueWithCommas - il valore espresso nel formato (X,y)
	 * @return il valore formattato per il database
	 */
	public static String convertValueFormatForDatabase(String valueWithCommas)
	{
		return valueWithCommas.replace(',', '.');
	}
	
	/**
	 * Questa funzione converte un valore monetario dal formato database (es. 100.00)  al formato 
	 * visualizzato (es. 100,00)
	 * @param valueWithDots - il valore espresso nel formato (X.y)
	 * @return il valore formattato per il database
	 */
	public static String convertValueFormatForViewing(String valueWithDots)
	{
		return valueWithDots.replace('.', ',');
	}
	
	/**
	 * Questa funzione ritorna un array di Stringhe contenente le ore lavorative di una giornata
	 * @return
	 */
	public static String[] getHoursOfADay()
	{
		String [] hours = new String[12];
		int hour = 8;
		for(int i=0; i<12; i++)
		{
			hours[i] = (hour+i) +"";
		}
		return hours;
	}
}
